Postgress 使用笔记

在使用 Docker 部署的 PostgreSQL 中，导出数据的过程与普通 PostgreSQL 数据库类似，但需要通过 Docker 容器来操作。以下是具体步骤：

---

1. 确认 PostgreSQL 容器正在运行

首先，确保 PostgreSQL 容器正在运行。

运行以下命令查看容器列表：

docker ps

找到 PostgreSQL 容器的 CONTAINER ID 或 NAME，例如 postgres-container。

---

2. 进入 PostgreSQL 容器

使用 docker exec 命令进入 PostgreSQL 容器的终端：

docker exec -it <container_name_or_id> bash

例如：

docker exec -it postgres-container bash

进入容器后，你可以使用 PostgreSQL 提供的命令行工具（如 pg_dump）进行数据导出。

---

3. 使用 pg_dump 导出数据

进入容器后，使用以下命令导出 PostgreSQL 数据。

导出单个数据库

使用 pg_dump 导出特定数据库：

pg_dump -U <用户名> -d <数据库名> -F c -f /tmp/<导出文件名>.dump

示例：

pg_dump -U postgres -d mydb -F c -f /tmp/mydb.dump

• -U：指定 PostgreSQL 用户名（如 postgres）。
• -d：指定要导出的数据库名（如 mydb）。
• -F c：指定导出格式为自定义格式（c）。
• -f：指定导出文件的路径（如 /tmp/mydb.dump）。

导出为 SQL 脚本

如果需要导出为 SQL 文件：

pg_dump -U postgres -d mydb -F p -f /tmp/mydb.sql

导出整个数据库集群

如果需要导出容器中所有的数据库，可以使用 pg_dumpall：

pg_dumpall -U postgres -f /tmp/all_databases.sql

---

4. 将导出的文件拷贝到主机

导出完成后，文件会保存在容器内部。需要将文件拷贝到主机上。

使用 docker cp 命令：

docker cp <container_name_or_id>:/tmp/<导出文件名>.dump <主机路径>

例如：

docker cp postgres-container:/tmp/mydb.dump /home/user/mydb.dump

---

5. 退出容器

完成操作后，可以退出容器：

exit

---

6. 直接从主机导出数据（可选）

如果不想进入容器，也可以直接在主机上运行 docker exec 命令来导出数据。

导出单个数据库

docker exec -i <container_name_or_id> pg_dump -U <用户名> -d <数据库名> -F c > <主机文件路径>

示例：

docker exec -i postgres-container pg_dump -U postgres -d mydb -F c > /home/user/mydb.dump

导出为 SQL 脚本

docker exec -i postgres-container pg_dump -U postgres -d mydb -F p > /home/user/mydb.sql

导出整个数据库集群

docker exec -i postgres-container pg_dumpall -U postgres > /home/user/all_databases.sql

---

7. 导出表为 CSV（可选）

如果只需要导出某张表的数据为 CSV，可以使用以下命令：

docker exec -i <container_name_or_id> psql -U <用户名> -d <数据库名> -c "\COPY <表名> TO '/tmp/<表名>.csv' CSV HEADER"

然后将 CSV 文件从容器拷贝到主机：

docker cp <container_name_or_id>:/tmp/<表名>.csv <主机路径>

---

总结

1. 登录容器：使用 docker exec 进入容器。
2. 使用 pg_dump 或 pg_dumpall：导出数据库或整个集群。
3. 拷贝文件到主机：使用 docker cp 将容器内的导出文件复制到主机。
4. 直接导出到主机（可选）：使用 docker exec 将数据直接导出到主机文件系统。

如果有其他问题，请随时告诉我！

以下是 PostgreSQL 的登录操作及常用查询命令的详细说明，包括如何登录数据库、基础操作和常用 SQL 查询。

---

1. 登录 PostgreSQL

1.1 登录到 PostgreSQL

在终端中使用 psql 命令登录 PostgreSQL 数据库。

本地登录

psql -U <用户名> -d <数据库名>

示例：

psql -U postgres -d mydb

• -U <用户名>：指定登录的数据库用户（如 postgres）。
• -d <数据库名>：指定要连接的数据库名称。

如果 PostgreSQL运行在不同的主机或端口上：

psql -h <主机地址> -p <端口号> -U <用户名> -d <数据库名>

示例：

psql -h 127.0.0.1 -p 5432 -U postgres -d mydb

1.2 登录到 PostgreSQL 容器

如果 PostgreSQL 运行在 Docker 容器中，可以通过以下方式登录：

1. 进入容器：

   docker exec -it <container_name_or_id> bash

2. 登录 PostgreSQL：

   psql -U <用户名> -d <数据库名>

或者直接运行：

docker exec -it <container_name_or_id> psql -U <用户名> -d <数据库名>

---

2. 常用命令

2.1 数据库相关操作

查看所有数据库

\l

切换数据库

\c <数据库名>

示例：

\c mydb

创建数据库

CREATE DATABASE <数据库名>;

示例：

CREATE DATABASE mydb;

删除数据库

DROP DATABASE <数据库名>;

示例：

DROP DATABASE mydb;

---

2.2 用户相关操作

查看所有用户

\du

创建用户

CREATE USER <用户名> WITH PASSWORD '<密码>';

示例：

CREATE USER myuser WITH PASSWORD 'mypassword';

删除用户

DROP USER <用户名>;

示例：

DROP USER myuser;

给用户赋予权限

GRANT ALL PRIVILEGES ON DATABASE <数据库名> TO <用户名>;

示例：

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

---

2.3 表相关操作

查看当前数据库中的所有表

\dt

创建表

CREATE TABLE <表名> (
    <列名1> <数据类型> [约束],
    <列名2> <数据类型> [约束],
    ...
);

示例：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);

删除表

DROP TABLE <表名>;

示例：

DROP TABLE users;

查看表结构

\d <表名>

示例：

\d users

---

2.4 插入、查询、更新和删除数据

插入数据

INSERT INTO <表名> (<列名1>, <列名2>, ...) VALUES (<值1>, <值2>, ...);

示例：

INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com');

查询数据

SELECT <列名1>, <列名2>, ... FROM <表名> WHERE <条件>;

示例：

SELECT * FROM users;
SELECT name, age FROM users WHERE age > 20;

更新数据

UPDATE <表名> SET <列名1>=<值1>, <列名2>=<值2>, ... WHERE <条件>;

示例：

UPDATE users SET age = 26 WHERE name = 'Alice';

删除数据

DELETE FROM <表名> WHERE <条件>;

示例：

DELETE FROM users WHERE age < 18;

---

2.5 备份与还原

使用 pg_dump 备份数据库

pg_dump -U <用户名> -d <数据库名> -F c -f <备份文件路径>

示例：

pg_dump -U postgres -d mydb -F c -f /path/to/backup.dump

使用 pg_restore 还原数据库

pg_restore -U <用户名> -d <数据库名> -F c <备份文件路径>

示例：

pg_restore -U postgres -d mydb -F c /path/to/backup.dump

使用 pg_dumpall 备份所有数据库

pg_dumpall -U <用户名> > <备份文件路径>

示例：

pg_dumpall -U postgres > /path/to/all_databases.sql

使用 psql 恢复所有数据库

psql -U <用户名> -f <备份文件路径>

示例：

psql -U postgres -f /path/to/all_databases.sql

---

2.6 退出 PostgreSQL

在 psql 命令行中，输入以下命令退出：

\q

---

2.7 其他常用命令

查看当前连接的数据库名

SELECT current_database();

查看当前用户

SELECT current_user;

查看数据库版本

SELECT version();

查看表中的前几行数据

SELECT * FROM <表名> LIMIT <行数>;

示例：

SELECT * FROM users LIMIT 10;

---

总结

• 登录数据库：使用 psql 或 docker exec 登录。
• 常用命令：包括创建/删除数据库、用户管理、表操作、数据查询等。
• 备份与还原：使用 pg_dump 和 pg_restore 进行数据库导入导出。

如果有其他需求或需要更详细的说明，请随时告诉我！

这个错误表明 PostgreSQL 客户端无法连接到服务器，因为它找不到运行中的 PostgreSQL 服务或对应的套接字文件。以下是解决问题的步骤：

---

1. 确认 PostgreSQL 是否已安装

• 在 Termux 中运行以下命令，检查 PostgreSQL 是否已正确安装：

  pkg install postgresql
• 如果 PostgreSQL 未安装，安装后再继续操作。

---

2. 初始化数据库

• 如果 PostgreSQL 是新安装的，你需要先初始化数据库：

  initdb $PREFIX/var/lib/postgresql
• 这一步会创建一个用于存储数据的 PostgreSQL 数据库目录。

---

3. 启动 PostgreSQL 服务

• PostgreSQL 需要一个服务来运行，确保它已启动：

  pg_ctl -D $PREFIX/var/lib/postgresql start
• 如果启动成功，应该会显示类似以下内容：

  server started
• 如果出现错误，请检查日志文件（通常位于 $PREFIX/var/lib/postgresql/pg_log）。

---

4. 检查套接字文件路径

• 默认情况下，PostgreSQL 的套接字文件会存储在 /tmp 或其他指定目录中。
• Termux 的 PostgreSQL 使用 $PREFIX/tmp 作为套接字目录，因此需要确保路径正确。
• 如果路径不正确，可以通过以下方式指定套接字路径：

  psql -h $PREFIX/tmp

---

5. 验证服务是否运行

• 使用以下命令检查 PostgreSQL 服务是否正在运行：

  pg_ctl status
• 如果服务未运行，尝试重新启动：

  pg_ctl -D $PREFIX/var/lib/postgresql restart

---

6. 检查端口和配置

• 确保 PostgreSQL 使用正确的端口（默认是 5432）。你可以通过编辑配置文件来检查：

  nano $PREFIX/var/lib/postgresql/postgresql.conf
• 确保以下设置正确：

  port = 5432
  unix_socket_directories = '/data/data/com.termux/files/usr/tmp'

---

7. 查看日志文件

• 如果问题仍然存在，查看 PostgreSQL 的日志文件以获取更多信息：

  cat $PREFIX/var/lib/postgresql/pg_log/*.log

---

8. 重建数据库目录（如果必要）

• 如果数据库目录损坏，可以尝试重新初始化：

  rm -rf $PREFIX/var/lib/postgresql
  initdb $PREFIX/var/lib/postgresql
  pg_ctl -D $PREFIX/var/lib/postgresql start

---

9. 尝试连接

• 一旦服务启动成功，尝试连接：

  psql -h $PREFIX/tmp -p 5432

---

总结

这个错误通常是由于 PostgreSQL 服务未启动或套接字路径配置错误引起的。通过以上步骤，你应该能够解决问题。如果问题仍然存在，请提供日志文件内容以便进一步排查。 PostgreSQL 提供了丰富的命令行工具，用于数据库的管理、备份、恢复、调试等操作。以下是 PostgreSQL 中常见的命令行工具及其使用说明：

---

1. initdb

• 功能：初始化数据库集群（创建 PostgreSQL 数据库的存储目录）。

• 常用语法：

  initdb [OPTION]... [DATADIR]

• 常用选项：

  • -D DATADIR：指定数据库的数据目录。
  • --username=USERNAME：指定超级用户名称（默认是当前用户）。
  • --auth=METHOD：指定本地用户的认证方法（如 trust, password, md5 等）。
  • --no-locale：不使用区域设置，默认使用 C 区域。
  • --encoding=ENCODING：指定数据库编码（如 UTF8）。

• 示例：

  initdb -D /var/lib/postgresql/data --encoding=UTF8 --auth=md5

---

2. pg_ctl

• 功能：用于启动、停止和检查 PostgreSQL 数据库服务器的状态。

• 常用语法：

  pg_ctl [OPTION]... {start|stop|restart|reload|status}

• 常用选项：

  • -D DATADIR：指定数据库的数据目录。
  • -l FILENAME：指定日志文件。
  • -w：等待操作完成。
  • -m MODE：指定停止模式（smart, fast, immediate）。

• 示例：

  pg_ctl -D /var/lib/postgresql/data start
  pg_ctl -D /var/lib/postgresql/data stop -m fast
  pg_ctl -D /var/lib/postgresql/data status

---

3. psql

• 功能：PostgreSQL 的交互式命令行客户端，用于执行 SQL 查询和管理数据库。

• 常用语法：

  psql [OPTION]... [DBNAME [USERNAME]]

• 常用选项：

  • -h HOSTNAME：指定数据库主机。
  • -p PORT：指定数据库端口。
  • -U USERNAME：指定连接的用户名。
  • -d DBNAME：指定连接的数据库名称。
  • -f FILENAME：从文件中读取 SQL 脚本并执行。
  • -c COMMAND：执行单个 SQL 命令后退出。

• 示例：

  psql -U postgres -d mydb
  psql -h localhost -p 5432 -U postgres -c "SELECT * FROM mytable;"
  psql -f script.sql

---

4. pg_dump

• 功能：备份单个数据库为 SQL 脚本或二进制格式。

• 常用语法：

  pg_dump [OPTION]... [DBNAME]

• 常用选项：

  • -U USERNAME：指定连接的用户名。
  • -f FILENAME：将输出保存到文件。
  • -F FORMAT：指定输出格式（p 为纯文本，c 为自定义格式，d 为目录格式，t 为 tar 格式）。
  • -t TABLE：仅导出指定的表。
  • --data-only：仅导出数据，不导出表结构。
  • --schema-only：仅导出表结构，不导出数据。

• 示例：

  pg_dump -U postgres -d mydb -f backup.sql
  pg_dump -U postgres -d mydb -F c -f backup.dump
  pg_dump -U postgres -d mydb -t mytable -f table_backup.sql

---

5. pg_restore

• 功能：从 pg_dump 的自定义格式或目录格式备份中恢复数据。

• 常用语法：

  pg_restore [OPTION]... [FILENAME]

• 常用选项：

  • -d DBNAME：指定要恢复到的数据库。
  • -C：在恢复前创建数据库。
  • -j NJOBS：指定并行恢复的作业数。
  • -l：列出备份文件中的内容。
  • -t TABLE：仅恢复指定的表。

• 示例：

  pg_restore -U postgres -d mydb backup.dump
  pg_restore -U postgres -C -d postgres backup.dump
  pg_restore -U postgres -d mydb -t mytable table_backup.dump

---

6. createdb

• 功能：创建一个新的数据库。

• 常用语法：

  createdb [OPTION]... [DBNAME]

• 常用选项：

  • -U USERNAME：指定连接的用户名。
  • -E ENCODING：指定数据库的编码。
  • --locale=LOCALE：指定区域设置。
  • -T TEMPLATE：指定数据库模板。

• 示例：

  createdb -U postgres mydb
  createdb -U postgres -E UTF8 --locale=en_US.UTF-8 mydb

---

7. dropdb

• 功能：删除一个数据库。

• 常用语法：

  dropdb [OPTION]... DBNAME

• 常用选项：

  • -U USERNAME：指定连接的用户名。
  • -i：交互模式，删除前提示确认。

• 示例：

  dropdb -U postgres mydb

---

8. pg_basebackup

• 功能：用于创建整个数据库集群的基础备份。

• 常用语法：

  pg_basebackup [OPTION]...

• 常用选项：

  • -D DIRECTORY：指定备份存储目录。
  • -F FORMAT：指定备份格式（p 为纯文本，t 为 tar 格式）。
  • -X METHOD：指定 WAL 日志处理方式（fetch 或 stream）。
  • -z：启用压缩。
  • -P：显示进度条。

• 示例：

  pg_basebackup -D /backup -F tar -z -P

---

9. pg_isready

• 功能：检查 PostgreSQL 数据库服务器是否可用。

• 常用语法：

  pg_isready [OPTION]...

• 常用选项：

  • -h HOSTNAME：指定数据库主机。
  • -p PORT：指定数据库端口。
  • -U USERNAME：指定连接的用户名。

• 示例：

  pg_isready -h localhost -p 5432 -U postgres

---

10. vacuumdb

• 功能：清理数据库，回收无用空间并更新统计信息。

• 常用语法：

  vacuumdb [OPTION]... [DBNAME]

• 常用选项：

  • -z：分析数据库并更新统计信息。
  • -f：强制执行全量清理。
  • -t TABLE：仅清理指定表。

• 示例：

  vacuumdb -U postgres -z mydb

---

11. reindexdb

• 功能：重新构建数据库索引。

• 常用语法：

  reindexdb [OPTION]... [DBNAME]

• 常用选项：

  • -t TABLE：重新索引指定表。
  • --system：重新索引系统表。

• 示例：

  reindexdb -U postgres mydb

---

12. pg_dumpall

• 功能：备份整个数据库集群（包括所有数据库和全局对象，如角色和权限）。

• 常用语法：

  pg_dumpall [OPTION]...

• 常用选项：

  • -f FILENAME：将输出保存到文件。
  • --globals-only：仅备份全局对象（如角色）。

• 示例：

  pg_dumpall -U postgres -f all_databases.sql

---

13. pg_config

• 功能：显示 PostgreSQL 的编译和安装信息。
• 常用语法：

  pg_config
• 示例：

  pg_config

---

总结

以上列出了 PostgreSQL 的主要命令行工具及其常用选项和使用示例。这些工具涵盖了数据库初始化、备份、恢复、清理、优化、监控等各个方面，是管理 PostgreSQL 数据库的核心工具集。如果需要更详细的帮助，可以通过 --help 查看每个命令的完整选项说明。

在 PostgreSQL 中，配置远程登录密码需要进行以下几个步骤，包括修改配置文件、创建或修改用户密码，以及确保远程访问权限正确设置。以下是详细的配置步骤：

---

1. 修改 PostgreSQL 配置以允许远程连接

默认情况下，PostgreSQL 只允许本地连接。如果需要远程连接，需要修改 postgresql.conf 和 pg_hba.conf 文件。

1.1 修改 postgresql.conf 文件

1. 找到 postgresql.conf 文件，通常位于 PostgreSQL 数据目录下（例如 /var/lib/pgsql/data/ 或 /etc/postgresql/<version>/main/）。

2. 打开文件：

   nano /path/to/postgresql.conf

3. 找到以下配置项：

   #listen_addresses = 'localhost'

4. 修改为：

   listen_addresses = '*'
   • * 表示监听所有 IP 地址。如果只允许特定 IP，可以用逗号分隔指定 IP（如 listen_addresses = '192.168.1.100'）。

5. 保存并退出。

---

1.2 修改 pg_hba.conf 文件

1. 找到 pg_hba.conf 文件，通常与 postgresql.conf 文件位于同一目录。

2. 打开文件：

   nano /path/to/pg_hba.conf

3. 添加以下内容到文件的末尾：

   # Allow remote connections with password authentication
   host    all             all             0.0.0.0/0               md5
   • host：表示允许远程连接。
   • all：表示允许连接所有数据库和所有用户。
   • 0.0.0.0/0：表示允许所有 IP 地址访问。如果需要限制特定 IP 段，可以替换为具体的范围（如 192.168.1.0/24）。
   • md5：表示使用 MD5 加密的密码验证。

4. 保存并退出。

---

1.3 重启 PostgreSQL 服务

完成修改后，需要重启 PostgreSQL 服务以使更改生效。

• 如果使用 systemd 管理 PostgreSQL 服务：

  sudo systemctl restart postgresql

• 如果使用 pg_ctl：

  pg_ctl restart -D /path/to/data

---

2. 设置 PostgreSQL 用户密码

PostgreSQL 的用户（也叫角色）需要设置密码才能通过远程登录。

2.1 登录到 PostgreSQL

1. 使用本地超级用户登录 PostgreSQL：

   psql -U postgres

   或者：

   sudo -u postgres psql

2. 确保你已经创建了一个用户。如果没有用户，可以通过以下命令创建一个新用户：

   CREATE USER username WITH PASSWORD 'password';
   ALTER USER username WITH LOGIN;

2.2 设置或修改用户密码

• 如果用户已经存在，可以通过以下命令设置或修改密码：

  ALTER USER username WITH PASSWORD 'new_password';
• 示例：

  ALTER USER myuser WITH PASSWORD 'mypassword';

---

3. 测试远程连接

完成配置后，可以通过以下方式测试远程连接。

3.1 使用 psql 测试

• 在远程机器上，使用以下命令连接到 PostgreSQL：

  psql -h <server_ip> -p 5432 -U username -d dbname
  • <server_ip>：PostgreSQL 服务器的 IP 地址。
  • 5432：PostgreSQL 的默认端口号。
  • username：登录的 PostgreSQL 用户名。
  • dbname：需要连接的数据库名称。

• 如果连接成功，会进入 psql 的交互式命令行。

---

3.2 使用图形化工具测试

可以使用图形化工具（如 pgAdmin, DBeaver, DataGrip）测试远程连接：

1. 打开工具并创建一个新的连接。
2. 填写以下信息：
   • Host：服务器的 IP 地址或域名。
   • Port：默认是 5432。
   • Database：需要连接的数据库名称。
   • Username：PostgreSQL 用户名。
   • Password：用户密码。
3. 测试连接。

---

4. 常见问题排查

4.1 防火墙问题

确保服务器防火墙允许 PostgreSQL 的默认端口（5432）对外开放。如果防火墙阻止了流量，远程连接将失败。

检查防火墙规则

• 使用 ufw（Ubuntu/Debian 系统）：

  sudo ufw allow 5432/tcp
  sudo ufw reload

• 使用 firewalld（CentOS/RHEL 系统）：

  sudo firewall-cmd --add-port=5432/tcp --permanent
  sudo firewall-cmd --reload

---

4.2 检查 PostgreSQL 服务是否运行

确保 PostgreSQL 服务正在运行并监听正确的端口。

检查服务状态

• 使用 systemctl：

  sudo systemctl status postgresql

检查监听端口

• 使用 netstat 或 ss：

  netstat -tuln | grep 5432
  或

  ss -tuln | grep 5432
  确保 PostgreSQL 正在监听 5432 端口。

---

4.3 检查日志文件

如果配置完成后仍然无法连接，可以查看 PostgreSQL 的日志文件，排查问题原因。

日志文件位置

• 默认日志文件可能位于 /var/log/postgresql/ 或数据目录下的 pg_log/。
• 打开日志文件查看错误信息：

  tail -f /path/to/logfile

---

总结

1. 修改 postgresql.conf 和 pg_hba.conf 文件以允许远程连接。
2. 设置或修改用户密码，并确保用户有登录权限。
3. 确保防火墙和网络配置允许远程访问 PostgreSQL 的端口。
4. 测试远程连接，并根据需要调整配置。
5. 如果遇到问题，检查 PostgreSQL 的日志文件和服务状态。

通过以上步骤，你应该能够成功配置 PostgreSQL 允许远程登录并使用密码进行身份验证。